/**
	AUTHOR		: Srikanth.
	DATE		: Dec 13, 2005
	USAGE           : EXEC sp_sort_oracle_transaction_dump_report '12/01/2004' ,'' 
			  EXEC sp_sort_oracle_transaction_dump_report '' ,''
	DESCRIPTION	: WHEN A LOT IS MOVED FROM ONE PROCESS TO ANOTHER PROCESS A TRANSACTION IS ENTERED IN THE ORACLE SYS.
			 ** THIS PROC TRACES ALL THE TRANSACTIONS UPTO ASSEMBLY IN THE CHIPTRAC SYSTEM FROM THE DATE ENTERED BY THE USER.

	LIMITATION 	: WE ARE RETRIVING THE ENTIRE TRACE FROM THE FAB PROCESS IF ANY TRANSACTION IS DONE IN THE SELECTED PERIOD.
			  WE ARE NOT CONSIDERING THE DATE OUT ENTERED BY USER AS DATE OUT IS NOT UPDATED FOR ALL PROCESS.
			  WE ARE NOT CONSIDERING THE SPLIT LOTS AS SINGLE LOT.CURRENTLY WE ARE NOT SHOWING SPLIT INTO.
			  WE ARE NOT CONSIDERING MRB,RMA AND DOWNGRADING CONCEPTS CURRENTLY.
*/

alter  PROC sp_sort_oracle_transaction_dump_report 
 
@from_date VARCHAR(20),
@to_date   VARCHAR(20)

as 
begin

DECLARE @RESULTS TABLE ( FAB_LOT_NUMBER 	VARCHAR(50),
			 PROCESS_WAFER_STORE 	VARCHAR(50),
			 WAFER_STORE_QTY_IN 	INT,
			 PROCESS_SORT		VARCHAR(50),
			 SORT_PART_NUMBER	VARCHAR(50),
			 SORT_VENDOR		VARCHAR(50),
			 SORT_DATE_IN		DATETIME,
			 SORT_DATE_OUT		DATETIME,
			 SORT_QTY_IN		INT,
			 SORT_QTY_OUT		INT,
			 DB_PROCESS		VARCHAR(50),
			 DB_VENDOR		VARCHAR(50),
			 DB_QTY_IN		INT,
			 DB_QTY_OUT		INT,
			 ASSEMBLY_LOT_NUMBER	VARCHAR(50) )

DECLARE @FAB_LOT_NUMBER 	VARCHAR(50)
DECLARE @PROCESS_WAFER_STORE 	VARCHAR(50)
DECLARE @WAFER_STORE_QTY_IN 	INT
DECLARE @SORT_PART_NUMBER	VARCHAR(50)
DECLARE @SORT_VENDOR		VARCHAR(50)
DECLARE @SORT_DATE_IN		DATETIME
DECLARE @SORT_DATE_OUT		DATETIME
DECLARE @SORT_QTY_IN		INT
DECLARE @SORT_QTY_OUT		INT
DECLARE @DB_QTY_IN		INT
DECLARE @DB_QTY_OUT		INT
DECLARE @PROCESS_SORT		VARCHAR(50)
DECLARE @DB_PROCESS		VARCHAR(50)
DECLARE @ASSEMBLY_LOT_NUMBER	VARCHAR(50)
DECLARE @ASSEMBLY_LOT_NUMBERS	VARCHAR(50)
DECLARE @DB_VENDOR		VARCHAR(50)

DECLARE @lot_id INT
DECLARE @status VARCHAR(50)

DECLARE @LOTS_TABLE TABLE   ([LOT_NUMBER] VARCHAR(50))
DECLARE @lot_cursor		CURSOR

	--Lots at Wafer Store for the given Date. 
	INSERT INTO @LOTS_TABLE SELECT DISTINCT LOT_NUMBER FROM LOT_TABLE LT, INVENTORY_LOT IL, PROCESS_STEP PS
		WHERE LT.LOT_ID = IL.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = 'Wafer Store'
		AND CONVERT(DATETIME, IL_DATE_IN ) >= CONVERT(DATETIME, @from_date ) 

	--Lots at Sort for the given Date.
	INSERT INTO @LOTS_TABLE SELECT LOT_NUMBER FROM SORT_RELEASE_TABLE SRT, SORT_RELEASE_LOT_TABLE SRLT 
		WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID AND STATUS <> 'Cancelled'
		AND CONVERT(DATETIME, SORT_REL_DATE ) >= CONVERT(DATETIME, @from_date )

	--SELECT DISTINCT LOT_NUMBER FROM @LOTS_TABLE
	SET @lot_cursor = CURSOR FOR SELECT DISTINCT LOT_NUMBER FROM @LOTS_TABLE
	OPEN @lot_cursor
	FETCH NEXT FROM @lot_cursor INTO @FAB_LOT_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--GETTING WAFER STORE LOT ID AND QTY FROM INVENTORY LOT AND FROM ITS HISTORY.
		SELECT @lot_id = LOT_ID
		FROM LOT_TABLE LT, PROCESS_STEP PS
		WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND PS_TYPE_NAME = 'Wafer Store' AND LOT_NUMBER = @FAB_LOT_NUMBER
		SELECT @WAFER_STORE_QTY_IN = IL_WAFER_QTY_IN FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID
		
		IF( @WAFER_STORE_QTY_IN IS NULL OR @WAFER_STORE_QTY_IN = 0  )
		BEGIN
			SELECT TOP 1 @WAFER_STORE_QTY_IN = IL_WAFER_QTY_IN FROM INVENTORY_LOT_HISTORY WHERE LOT_ID = @LOT_ID ORDER BY TIMESTAMP 
		END		

		INSERT INTO @RESULTS ( FAB_LOT_NUMBER, PROCESS_WAFER_STORE, WAFER_STORE_QTY_IN ) 
			       VALUES( @FAB_LOT_NUMBER,'Wafer Store'     , @WAFER_STORE_QTY_IN ) 
		-- GETTING SORT RELEATED INFORMATION
		SELECT @SORT_DATE_IN = MIN( SORT_REL_DATE ), @SORT_QTY_IN = SUM( QTY_PULLED ), @SORT_PART_NUMBER = ( SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = SRT.PROCESSSTEP_ID ),
		@SORT_VENDOR = (SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = VEND_ID)
		FROM SORT_RELEASE_TABLE SRT, SORT_RELEASE_LOT_TABLE SRLT 
		WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID AND LOT_ID = @lot_id 
		AND STATUS <> 'Cancelled'
		GROUP BY LOT_ID, PROCESSSTEP_ID,VEND_ID

		SET @lot_id = NULL
		IF @SORT_QTY_IN > 0 
		BEGIN
			SET @PROCESS_SORT = 'Sort'
			--Getting the Lot Id at Sort Stage for the Lot Number
			SELECT @lot_id = LOT_ID, @status = LOT_CURRENT_STATUS/*, @DB_VENDOR = ( SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID )*/ FROM LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = 'Sort' AND LOT_NUMBER = @FAB_LOT_NUMBER
			
			IF ( @lot_id IS NOT NULL AND @lot_id > 0 ) 
			BEGIN
		
				IF ( @status IS NOT NULL AND @status = 'Close' )
				BEGIN
					SELECT TOP 1 @SORT_DATE_OUT = CONVERT(VARCHAR, ISNULL( SL_DATE_OUT, TIMESTAMP), 101) FROM SORT_LOT_HISTORY WHERE LOT_ID = @lot_id ORDER BY TIMESTAMP DESC
				END
					-- NEED TO CHECH HERE WHETHER TO SHOW QTY OUT AS WAFER QTY OR DIE QTY.
					SELECT @SORT_QTY_OUT = COUNT(WAFER_ID) FROM INFORMATIVE_BINNING WHERE LOT_ID = @lot_id and good_dice > 0
					--SELECT @SORT_QTY_OUT = SUM( GOOD_DICE ) FROM INFORMATIVE_BINNING WHERE LOT_ID = @lot_id and good_dice > 0
				/*
				--Gathering Lot's Information when the Lot is at Die Bank Process.
				*/
				
				SELECT @DB_QTY_IN = SUM(GOOD_DICE) FROM INFORMATIVE_BINNING WHERE LOT_ID = @lot_id
		
				IF ( @DB_QTY_IN <> '' AND @DB_QTY_IN > 0 )
				BEGIN
					SET @DB_PROCESS = 'Die Bank'
					SELECT @DB_VENDOR = ( SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID ) FROM LOT_TABLE LT WHERE LT.LOT_ID = @LOT_ID
					SELECT @DB_QTY_OUT = SUM( QTY_PULLED ) FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'Cancelled' and ARLT.LOT_ID = @lot_id
					--SET @ASSEMBLY_LOT_NUMBERS = null
					DECLARE @assm_cursor CURSOR							
					SET @assm_cursor = CURSOR FOR SELECT DISTINCT ASSEMBLY_RELEASE_NO FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT 
								WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'Cancelled' and ARLT.LOT_ID = @lot_id
					OPEN @assm_cursor
					FETCH NEXT FROM @assm_cursor INTO @ASSEMBLY_LOT_NUMBER
					WHILE @@FETCH_STATUS = 0
					BEGIN	
						IF ( @ASSEMBLY_LOT_NUMBERS IS NULL )
						BEGIN
							SET @ASSEMBLY_LOT_NUMBERS = @ASSEMBLY_LOT_NUMBER  
						END
						ELSE
						BEGIN
							SET @ASSEMBLY_LOT_NUMBERS = @ASSEMBLY_LOT_NUMBERS +','+ @ASSEMBLY_LOT_NUMBER  
						END
						FETCH NEXT FROM @assm_cursor INTO @ASSEMBLY_LOT_NUMBER
					END

					/*IF ( LEN( LTRIM ( RTRIM ( @ASSEMBLY_LOT_NUMBER ) ) ) > 0 )
					BEGIN
						SET @ASSEMBLY_LOT_NUMBER = SUBSTRING( @ASSEMBLY_LOT_NUMBER, 0, LEN( @ASSEMBLY_LOT_NUMBER ) )
						
						UPDATE @RESULTS SET ASSEMBLY_LOT_NUMBER = @ASSEMBLY_LOT_NUMBER WHERE FAB_LOT_NUMBER = @FAB_LOT_NUMBER
					END*/

					CLOSE @assm_cursor
					DEALLOCATE @assm_cursor

				END
			END
		END

		UPDATE @RESULTS SET PROCESS_SORT 	= @PROCESS_SORT,
				    SORT_PART_NUMBER 	= @SORT_PART_NUMBER,
				    SORT_VENDOR 	= @SORT_VENDOR,
				    SORT_DATE_IN 	= @SORT_DATE_IN,
				    SORT_DATE_OUT 	= @SORT_DATE_OUT,
				    SORT_QTY_IN 	= @SORT_QTY_IN,
				    SORT_QTY_OUT 	= @SORT_QTY_OUT,
				    DB_PROCESS 		= @DB_PROCESS,
				    DB_VENDOR		= @DB_VENDOR,
				    DB_QTY_IN 		= @DB_QTY_IN,
				    DB_QTY_OUT 		= @DB_QTY_OUT,
				    ASSEMBLY_LOT_NUMBER = @ASSEMBLY_LOT_NUMBERS

		WHERE FAB_LOT_NUMBER = @FAB_LOT_NUMBER

		SET @FAB_LOT_NUMBER	= NULL
		SET @WAFER_STORE_QTY_IN = NULL
		SET @SORT_PART_NUMBER	= NULL
		SET @SORT_VENDOR	= NULL
		SET @SORT_DATE_IN	= NULL
		SET @SORT_DATE_OUT	= NULL
		SET @SORT_QTY_IN	= NULL
		SET @SORT_QTY_OUT	= NULL
		SET @DB_QTY_IN		= NULL
		SET @DB_QTY_OUT		= NULL
		SET @ASSEMBLY_LOT_NUMBER= NULL
		SET @PROCESS_SORT	= NULL
		SET @DB_PROCESS		= NULL
		SET @ASSEMBLY_LOT_NUMBERS= NULL
		SET @DB_VENDOR		= NULL

	FETCH NEXT FROM @lot_cursor INTO @FAB_LOT_NUMBER
	END
	CLOSE @lot_cursor
	DEALLOCATE @lot_cursor
	
	--SELECT * FROM @RESULTS 
	SELECT FAB_LOT_NUMBER AS [LOT NUMBER],PROCESS_WAFER_STORE AS PROCESS,WAFER_STORE_QTY_IN AS [QTY IN],
		PROCESS_SORT AS PROCESS ,SORT_PART_NUMBER AS OPN,SORT_VENDOR AS VENDOR,
		LEFT(CONVERT(VARCHAR,SORT_DATE_IN,101),5) AS [DATE IN],LEFT(CONVERT(VARCHAR,SORT_DATE_OUT,101),5) AS [DATE OUT],
		SORT_QTY_IN AS [QTY IN],SORT_QTY_OUT AS [QTY OUT],/*DB_PROCESS AS PROCESS,*/DB_VENDOR AS VENDOR, DB_QTY_IN AS [QTY IN],
		DB_QTY_OUT AS [QTY OUT],ASSEMBLY_LOT_NUMBER AS [ASSM REL] FROM @RESULTS ORDER BY SORT_DATE_OUT DESC
end
